package model

import (
	"common/helper"
	myredis "common/redis"
	"database/sql"
	"errors"
	"fmt"
	g "github.com/doug-martin/goqu/v9"
	"github.com/valyala/fasthttp"
	"strconv"
	"strings"
	"time"
)

type BatchUpdateStateParam struct {
	State    string `json:"state" db:"state"`
	ParentId string `json:"parent_id" db:"parent_id"`
}

func ProxyDetailList(param ReportProxyParam, ctx *fasthttp.RequestCtx) (ReportProxyRetData, error) {
	data := ReportProxyRetData{}
	where := "1=1"
	oderBy := "created_at"
	oderType := "desc"
	if param.ProxyId != "" {
		where += " and a.uid=" + param.ProxyId
	}
	if param.ParentId > 0 {
		where += " and b.parent_id=" + strconv.Itoa(param.ParentId)
	}
	if param.StartTime != "" {
		where += " and a.created_at>='" + param.StartTime + "'"
	}
	if param.EndTime != "" {
		where += " and a.created_at<='" + param.EndTime + "'"
	}
	if param.OperatorId != "" {
		where += " and c.id=" + param.OperatorId
	}
	if param.BusinessId != "" {
		where += " and d.id=" + param.BusinessId
	}
	if param.OderBY != "" {
		oderBy = param.OderBY
	}
	if param.OderType != "" {
		oderType = param.OderType
	}

	loginUser := GetLoginUser(ctx)
	if loginUser.Operator != "" {
		where += " and c.id=" + loginUser.Operator
	}
	if loginUser.Businsess != "" {
		where += " and d.id=" + loginUser.Businsess
	}

	join := " left join tbl_member_base as b on b.uid=a.uid"          //玩家表
	join += " left join tbl_operator_info as c on c.id=b.operator_id" //渠道表
	join += " left join tbl_business_info as d on d.id=b.business_id" //业务员表
	order := oderBy + " " + oderType

	offset := (param.Page - 1) * param.PageSize
	if param.Page >= 1 {
		count := "select count(1) from tbl_report_proxy_daily as a " + join + " where " + where

		err := meta.MerchantDB.Get(&data.T, count)
		if err != nil {

			return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), count), "数据库错误")
		}

		if data.T == 0 {
			return data, nil
		}
	}
	field := "a.*," +
		"ifnull(b.username,'') username," +
		"ifnull(c.id,'') operator_id," +
		"ifnull(c.operator_name,'') operator_name," +
		"ifnull(d.id,'') business_id," +
		"ifnull(d.account_name,'') as business_name"
	query := "select " + field + " from tbl_report_proxy_daily  as a " + join + " where " + where + " order by " + order + " limit " + strconv.Itoa(param.PageSize) + " offset " + strconv.Itoa(offset)

	err := meta.MerchantDB.Select(&data.D, query)
	if err != nil && err != sql.ErrNoRows {

		return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), query), "数据库错误")
	}

	return data, nil
}

func ProxyDailyList(param ReportProxyParam, ctx *fasthttp.RequestCtx) (ReportProxyDailyRetData, error) {
	data := ReportProxyDailyRetData{}
	where := "a.level=1"
	oderBy := "a.created_at"
	oderType := "desc"
	if param.ProxyId != "" {
		if param.ProxyId != "-1" {
			where += " and a.uid=" + param.ProxyId
		}

	} else {
		return data, nil
	}
	//if param.StartTime != "" {
	// StartTime, _ := helper.TimeToLoc(param.StartTime, loc)
	// where += " and b.created_at>='" + strconv.FormatInt(StartTime, 10) + "'"
	//}
	//if param.EndTime != "" {
	// EndTime, _ := helper.TimeToLoc(param.EndTime, loc)
	// where += " and b.created_at<='" + strconv.FormatInt(EndTime, 10) + "'"
	//}
	if param.StartTime != "" {
		where += " and a.created_at>='" + param.StartTime + "'"
	}
	if param.EndTime != "" {
		where += " and a.created_at<='" + param.EndTime + "'"
	}
	if param.OperatorId != "" {
		where += " and b.operator_id=" + param.OperatorId
	}
	if param.BusinessId != "" {
		where += " and b.business_id=" + param.BusinessId
	}
	if param.OderBY != "" {
		if param.OderBY == "team_deposit" {
			param.OderBY = "deposit"
		}
		if param.OderBY == "team_withdraw" {
			param.OderBY = "withdraw"
		}
		if param.OderBY == "team_running" {
			param.OderBY = "running"
		}
		oderBy = "a." + param.OderBY
	}
	if param.OderType != "" {
		oderType = param.OderType
	}

	loginUser := GetLoginUser(ctx)
	if loginUser.Operator != "" {
		where += " and b.operator_id=" + loginUser.Operator
	}
	if loginUser.Businsess != "" {
		where += " and b.business_id=" + loginUser.Businsess
	}
	table := "tbl_report_proxy_daily"

	join := " left join tbl_report_user_daily as b1 on b1.uid=a.uid and b1.created_at=a.created_at "
	join += " left join tbl_report_proxy_daily as b2 on b2.uid=a.uid and b2.created_at=a.created_at and b2.level=2"
	join += " left join tbl_report_proxy_daily as b3 on b3.uid=a.uid and b3.created_at=a.created_at and b3.level=3"

	join += " join tbl_member_base as b on b.uid=a.uid" //玩家表
	//join += " left join tbl_operator_info as c on c.id=b.operator_id" //渠道表
	//join += " left join tbl_business_info as d on d.id=b.business_id" //业务员表
	order := oderBy + " " + oderType

	offset := (param.Page - 1) * param.PageSize
	if param.Page >= 1 {

		count := "select count(1) from tbl_report_proxy_daily as a join tbl_member_base as b on b.uid=a.uid where " + where

		err := meta.MerchantDB.Get(&data.T, count)
		if err != nil {

			return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), count), "数据库错误")
		}

		if data.T == 0 {
			return data, nil
		}
	}
	field := "a.uid,a.created_at" +
		",ifnull(b1.proxy_invite_bonus + b1.enable_first_deposit_to_parent,0) proxy_invite_bonus" +
		",ifnull(b1.deposit,0) deposit" +
		",ifnull(b1.withdraw,0) withdraw" +
		",ifnull(b1.running,0) running" +
		",CONCAT(a.team_num,',',ifnull(b2.team_num,0),',',ifnull(b3.team_num,0)) team_num" +
		",CONCAT(a.valid_num,',',ifnull(b2.valid_num,0),',',ifnull(b3.valid_num,0)) valid_num" +
		",CONCAT(a.first_deposit_num,',',ifnull(b2.first_deposit_num,0),',',ifnull(b3.first_deposit_num,0)) first_deposit_num" +
		",CONCAT(a.deposit,',',ifnull(b2.deposit,0),',',ifnull(b3.deposit,0)) team_deposit" +
		",CONCAT(a.withdraw,',',ifnull(b2.withdraw,0),',',ifnull(b3.withdraw,0)) team_withdraw" +
		",CONCAT(a.running,',',ifnull(b2.running,0),',',ifnull(b3.running,0)) team_running"

	query := "select " + field + " from " + table + "  as a " + join + " where " + where + " order by " + order + " limit " + strconv.Itoa(param.PageSize) + " offset " + strconv.Itoa(offset)

	err := meta.MerchantDB.Select(&data.D, query)
	if err != nil && err != sql.ErrNoRows {

		return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), query), "数据库错误")
	}
	//统计
	sfield := "ifnull(sum(a.proxy_invite_bonus + a.enable_first_deposit_to_parent),0) proxy_invite_bonus" +
		",ifnull(sum(a.first_deposit_num),0) first_deposit_num" +
		",ifnull(sum(a.valid_num),0) valid_num" +
		",ifnull(sum(a.deposit),0) deposit" +
		",sum(ifnull(b2.first_deposit_num,0) + ifnull(b3.first_deposit_num,0)) team_first_deposit_num" +
		",sum(ifnull(b2.valid_num,0) + ifnull(b3.valid_num,0)) team_valid_num" +
		",sum(ifnull(b2.deposit,0) + ifnull(b3.deposit,0)) team_deposit" +
		",sum(ifnull(a.running,0) + ifnull(b2.running,0) + ifnull(b3.running,0)) team_running"
	query = "select " + sfield + " from " + table + " as a " + join + " where " + where

	err = meta.MerchantDB.Get(&data.S, query)

	if err != nil && err != sql.ErrNoRows {
		return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), query), "数据库错误")
	}
	return data, nil
}

func ProxyStatisticsList(param ReportProxyParam, ctx *fasthttp.RequestCtx) (ReportProxyStatisticsRetData, error) {
	data := ReportProxyStatisticsRetData{}
	where := "1=1"
	oderBy := "a.id"
	oderType := "desc"
	if param.ProxyId != "" {
		where += " and a.uid=" + param.ProxyId
	}
	if param.Uid != "" {
		where += " and a.uid=" + param.Uid
	}
	if param.OderBY != "" {
		oderBy = param.OderBY
	}
	if param.OderType != "" {
		oderType = param.OderType
	}

	loginUser := GetLoginUser(ctx)
	if loginUser.Operator != "" {
		where += " and b.operator_id=" + loginUser.Operator
	}
	if loginUser.Businsess != "" {
		where += " and b.business_id=" + loginUser.Businsess
	}
	table := "tbl_member_accu"

	join := " join tbl_member_base as b on b.uid=a.uid" //玩家表

	join += " left join tbl_proxy_accu as b1 on b1.uid=a.uid and b1.level=1"
	join += " left join tbl_proxy_accu as b2 on b2.uid=a.uid and b2.level=2"
	join += " left join tbl_proxy_accu as b3 on b3.uid=a.uid and b3.level=3"

	//昨天
	join += " left join tbl_report_proxy_daily as c1 on c1.uid=a.uid and c1.level=1 and c1.created_at='" + time.Now().AddDate(0, 0, -1).Format("2006-01-02") + "'"

	order := oderBy + " " + oderType

	offset := (param.Page - 1) * param.PageSize
	if param.Page >= 1 {

		count := "select count(1) from " + table + " as a " + join + " where " + where

		err := meta.MerchantDB.Get(&data.T, count)
		if err != nil {

			return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), count), "数据库错误")
		}

		if data.T == 0 {
			return data, nil
		}
	}
	field := "a.uid,a.deposit,a.running,b.state" +
		",(ifnull(b1.team_num,0) + ifnull(b2.team_num,0) + ifnull(b3.team_num,0)) team_num" +
		",(ifnull(b1.deposit,0) + ifnull(b2.deposit,0) + ifnull(b3.deposit,0)) team_deposit" +
		",(ifnull(b1.deposit_num,0) + ifnull(b2.deposit_num,0) + ifnull(b3.deposit_num,0)) team_deposit_num" +
		",ifnull(b1.team_num,0) lv1_team_num,ifnull(b1.deposit,0) lv1_deposit,ifnull(b1.deposit_num,0) lv1_deposit_num,ifnull(b1.running,0) lv1_running" +
		",ifnull(b2.team_num,0) lv2_team_num,ifnull(b2.deposit,0) lv2_deposit,ifnull(b2.deposit_num,0) lv2_deposit_num,ifnull(b2.running,0) lv2_running" +
		",ifnull(b3.team_num,0) lv3_team_num,ifnull(b3.deposit,0) lv3_deposit,ifnull(b3.deposit_num,0) lv3_deposit_num,ifnull(b3.running,0) lv3_running" +
		",ifnull(c1.team_num,0) lv1_yes_num,ifnull(c1.deposit,0) lv1_yes_deposit"
	query := "select " + field + " from " + table + "  as a " + join + " where " + where + " order by " + order + " limit " + strconv.Itoa(param.PageSize) + " offset " + strconv.Itoa(offset)
	//fmt.Println(query)
	err := meta.MerchantDB.Select(&data.D, query)
	if err != nil && err != sql.ErrNoRows {

		return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), query), "数据库错误")
	}
	return data, nil
}

func ProxySummaryList(param ReportProxyParam, ctx *fasthttp.RequestCtx) (ReportProxyRetData, error) {
	data := ReportProxyRetData{}
	where := "1=1"
	oderBy := "created_at"
	oderType := "desc"
	if param.ProxyId != "" {
		where += " and a.uid=" + param.ProxyId
	}
	if param.ParentId > 0 {
		where += " and b.parent_id=" + strconv.Itoa(param.ParentId)
	}
	if param.StartTime != "" {
		where += " and a.created_at>='" + param.StartTime + "'"
	}
	if param.EndTime != "" {
		where += " and a.created_at<='" + param.EndTime + "'"
	}
	if param.OperatorId != "" {
		where += " and c.id=" + param.OperatorId
	}
	if param.BusinessId != "" {
		where += " and d.id=" + param.BusinessId
	}
	if param.OderBY != "" {
		oderBy = param.OderBY
	}
	if param.OderType != "" {
		oderType = param.OderType
	}

	loginUser := GetLoginUser(ctx)
	if loginUser.Operator != "" {
		where += " and c.id=" + loginUser.Operator
	}
	if loginUser.Businsess != "" {
		where += " and d.id=" + loginUser.Businsess
	}

	join := " left join tbl_member_base as b on b.uid=a.uid"          //玩家表
	join += " left join tbl_operator_info as c on c.id=b.operator_id" //渠道表
	join += " left join tbl_business_info as d on d.id=b.business_id" //业务员表
	order := oderBy + " " + oderType

	offset := (param.Page - 1) * param.PageSize
	if param.Page >= 1 {
		count := "select count(1) from tbl_proxy_accu as a " + join + " where " + where

		err := meta.MerchantDB.Get(&data.T, count)
		if err != nil {

			return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), count), "数据库错误")
		}

		if data.T == 0 {
			return data, nil
		}
	}
	field := "a.*," +
		"ifnull(b.username,'') username," +
		"ifnull(c.id,'') operator_id," +
		"ifnull(c.operator_name,'') operator_name," +
		"ifnull(d.id,'') business_id," +
		"ifnull(d.account_name,'') as business_name"
	query := "select " + field + " from tbl_proxy_accu  as a " + join + " where " + where + " order by " + order + " limit " + strconv.Itoa(param.PageSize) + " offset " + strconv.Itoa(offset)

	err := meta.MerchantDB.Select(&data.D, query)
	if err != nil && err != sql.ErrNoRows {

		return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), query), "数据库错误")
	}
	return data, nil
}

func ProxyRealtionList(param ReportProxyRelationParam, ctx *fasthttp.RequestCtx) (ReportProxyRelationRetData, error) {
	data := ReportProxyRelationRetData{}
	where := "a.tester=1"
	oderBy := "created_at"
	oderType := "desc"
	if param.Uid != "" {
		where += " and a.uid=" + param.Uid
	}
	if param.Pid != "" {
		where += " and f.parent_id=" + param.Pid
	}
	if param.IsRecharge == 1 {
		where += " and e.deposit>0"
	}
	if param.IsRecharge == 2 {
		where += " and e.deposit=0"
	}
	if param.RegIp != "" {
		where += " and a.created_ip='" + param.RegIp + "'"
	}
	if param.RegStartTime != "" {
		RegStartTime, _ := helper.TimeToLoc(param.RegStartTime, loc)
		where += " and a.created_at>='" + strconv.FormatInt(RegStartTime, 10) + "'"
	}
	if param.RegEndTime != "" {
		RegEndTime, _ := helper.TimeToLoc(param.RegEndTime, loc)
		where += " and a.created_at<='" + strconv.FormatInt(RegEndTime, 10) + "'"
	}

	if param.LoginStartTime != "" {
		LoginStartTime, _ := helper.TimeToLoc(param.LoginStartTime, loc)
		where += " and a.last_login_at>='" + strconv.FormatInt(LoginStartTime, 10) + "'"
	}
	if param.LoginEndTime != "" {
		LoginEndTime, _ := helper.TimeToLoc(param.LoginEndTime, loc)
		where += " and a.last_login_at<='" + strconv.FormatInt(LoginEndTime, 10) + "'"
	}

	if param.OderBY != "" {
		oderBy = param.OderBY
	}
	if param.OderType != "" {
		oderType = param.OderType
	}

	loginUser := GetLoginUser(ctx)
	if loginUser.Operator != "" {
		where += " and a.operator_id=" + loginUser.Operator
	}
	if loginUser.Businsess != "" {
		where += " and a.business_id=" + loginUser.Businsess
	}

	join := " left join tbl_deposit as b on b.uid=a.uid and b.is_first=1 and b.state in(" + strconv.Itoa(DepositSuccess) + "," + strconv.Itoa(DepositRepairSuccess) + ")" //充值表
	//join += " left join tbl_operator_info as c on c.id=a.operator_id"                                                                                                     //渠道表
	//join += " left join tbl_business_info as d on d.id=a.business_id"                                                                                                     //业务员表
	join += " left join tbl_member_accu as e on e.uid=a.uid" //统计表
	join += " left join tbl_proxy_base as f on f.uid=a.uid"  //代理表
	order := oderBy + " " + oderType

	offset := (param.Page - 1) * param.PageSize
	if param.Page >= 1 {
		count := "select count(1) from tbl_member_base as a " + join + " where " + where

		err := meta.MerchantDB.Get(&data.T, count)
		if err != nil {

			return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), count), "数据库错误")
		}

		if data.T == 0 {
			return data, nil
		}
	}

	field := "a.uid,a.created_at,a.created_ip,a.last_login_at,a.last_login_ip," +
		"ifnull(f.parent_id,0) pid," +
		"ifnull(f.DonateToParent,0) DonateToParent," +
		"ifnull(e.deposit,0) deposit," +
		"ifnull(e.withdraw,0) withdraw," +
		"ifnull(e.totalWaged,0) as totalWaged," +
		"ifnull(b.amount,0) first_deposit," +
		"ifnull(b.created_at,'') as first_deposit_time"
	query := "select " + field + " from tbl_member_base  as a " + join + " where " + where + " order by " + order + " limit " + strconv.Itoa(param.PageSize) + " offset " + strconv.Itoa(offset)

	err := meta.MerchantDB.Select(&data.D, query)
	if err != nil && err != sql.ErrNoRows {

		return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), query), "数据库错误")
	}
	for key, value := range data.D {
		data.D[key].Profit = value.Deposit - value.Withdraw
		Profit := fmt.Sprintf("%.2f", data.D[key].Profit)
		data.D[key].Profit, _ = strconv.ParseFloat(Profit, 64)
	}

	//统计
	sfield := "ifnull(sum(e.deposit),0) deposit," +
		"ifnull(sum(b.amount),0) first_deposit," +
		"ifnull(sum(e.withdraw),0) withdraw," +
		"ifnull(sum(f.DonateToParent),0) DonateToParent," +
		"ifnull(sum(e.game_tax),0) tax"
	query = "select " + sfield + " from tbl_member_base as a " + join + " where " + where

	err = meta.MerchantDB.Get(&data.S, query)

	if err != nil && err != sql.ErrNoRows {
		return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), query), "数据库错误")
	}
	(data.S).Profit = (data.S).Deposit - (data.S).Withdraw
	Profit := fmt.Sprintf("%.2f", (data.S).Profit)
	(data.S).Profit, _ = strconv.ParseFloat(Profit, 64)
	(data.S).Num = int32(data.T)
	return data, nil
}

func ChanggeParent(uid, parentId int, operatorId, BusinessId string, ctx *fasthttp.RequestCtx) error {
	tx, _ := meta.MerchantDB.Begin()

	//admin_info := session.AdminGet(ctx)
	//opt_id, _ := strconv.Atoi(admin_info["id"])
	//ip := helper.FromRequest(ctx)
	//InsertOptLog(1, opt_id, "下级被解绑/换绑", strconv.Itoa(beforeParentId), string(ctx.PostBody()), ip)

	query, _, _ := dialect.Update("tbl_proxy_base").Set(g.Record{"parent_id": parentId}).Where(g.Ex{"uid": uid}).ToSQL()
	_, err := meta.MerchantDB.Exec(query)
	if err != nil {
		tx.Rollback()
		return errors.New("数据库错误")
	}
	query, _, _ = dialect.Update("tbl_member_base").Set(g.Record{"operator_id": operatorId, "business_id": BusinessId}).Where(g.Ex{"uid": uid}).ToSQL()
	_, err = meta.MerchantDB.Exec(query)
	if err != nil {
		tx.Rollback()
		return errors.New("数据库错误")
	}

	//更新缓存
	if myredis.IsUserExis(strconv.Itoa(uid)) {
		ret := myredis.SetUserFieldValue(strconv.Itoa(uid), "tbl_proxy_base:parent_id", strconv.Itoa(parentId))
		if ret == false {
			tx.Rollback()
			return errors.New(helper.ServerErr)
		}
		ret = myredis.SetUserFieldValue(strconv.Itoa(uid), "tbl_member_base:operator_id", operatorId)
		if ret == false {
			tx.Rollback()
			return errors.New(helper.ServerErr)
		}
		ret = myredis.SetUserFieldValue(strconv.Itoa(uid), "tbl_member_base:business_id", BusinessId)
		if ret == false {
			tx.Rollback()
			return errors.New(helper.ServerErr)
		}
	}
	tx.Commit()
	return nil
}

func GetBolgerList(param ReportProxyParam, ctx *fasthttp.RequestCtx) (ReportBolgerRetData, error) {

	data := ReportBolgerRetData{}
	where := "1=1"
	oderBy := "a.bloger_date"
	oderType := "desc"
	if param.ProxyId != "" {
		where += " and a.uid=" + param.ProxyId
	}
	if param.ParentId > 0 {
		where += " and b.parent_id=" + strconv.Itoa(param.ParentId)
	}
	if param.StartTime != "" {
		StartTime, _ := helper.TimeToLoc(param.StartTime, loc)
		where += " and a.bloger_date>='" + strconv.FormatInt(StartTime, 10) + "'"
	}
	if param.EndTime != "" {
		EndTime, _ := helper.TimeToLoc(param.EndTime, loc)
		where += " and a.bloger_date<='" + strconv.FormatInt(EndTime, 10) + "'"
	}
	if param.OperatorId != "" {
		where += " and c.id=" + param.OperatorId
	}
	if param.BusinessId != "" {
		where += " and d.id=" + param.BusinessId
	}
	if param.OderBY != "" {
		oderBy = param.OderBY
	}
	if param.OderType != "" {
		oderType = param.OderType
	}

	loginUser := GetLoginUser(ctx)
	if loginUser.Operator != "" {
		where += " and c.id=" + loginUser.Operator
	}
	if loginUser.Businsess != "" {
		where += " and d.id=" + loginUser.Businsess
	}

	table := "tbl_bloger_list"

	join := " left join tbl_member_base as b on b.uid=a.uid"          //玩家表
	join += " left join tbl_operator_info as c on c.id=b.operator_id" //渠道表
	join += " left join tbl_business_info as d on d.id=b.business_id" //业务员表

	join += " left join tbl_member_accu as e on e.uid=a.uid"
	join += " left join view_member_adjust as f on f.uid=a.uid"
	join += " left join (select uid,sum(team_num) as team_num,sum(deposit_num) as deposit_num,sum(deposit) as deposit,sum(withdraw) as withdraw from tbl_proxy_accu group by uid) as g on g.uid=a.uid"

	order := oderBy + " " + oderType

	offset := (param.Page - 1) * param.PageSize
	if param.Page >= 1 {
		count := "select count(1) from " + table + " as a " + join + " where " + where

		err := meta.MerchantDB.Get(&data.T, count)
		if err != nil {

			return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), count), "数据库错误")
		}

		if data.T == 0 {
			return data, nil
		}
	}
	field := "a.uid,a.bloger_name,a.bloger_date" +
		",ifnull(e.withdraw,0) withdraw" +
		",ifnull(e.deposit,0) deposit" +
		",ifnull(f.amount,0) up_score" +
		",ifnull(g.team_num,0) team_num" +
		",ifnull(g.deposit_num,0) team_deposit_num" +
		",ifnull(g.deposit,0) team_deposit" +
		",ifnull(g.withdraw,0) team_withdraw" +
		",ifnull((ifnull(g.deposit,0)-ifnull(e.withdraw,0)-ifnull(g.withdraw,0)),0) profit" +
		",ifnull(case when e.withdraw=0 then -1 else ifnull(g.deposit,0)*100/e.withdraw end,-1) contribution_rate" +
		",ifnull(c.id,'') operator_id" +
		",ifnull(c.operator_name,'') operator_name" +
		",ifnull(d.id,'') business_id" +
		",ifnull(d.account_name,'') as business_name"

	query := "select " + field + " from " + table + "  as a " + join + " where " + where + " order by " + order + " limit " + strconv.Itoa(param.PageSize) + " offset " + strconv.Itoa(offset)
	//fmt.Println(query)
	err := meta.MerchantDB.Select(&data.D, query)
	if err != nil && err != sql.ErrNoRows {

		return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), query), "数据库错误")
	}
	return data, nil
}

func GetBolgerList2(param ReportProxyParam, ctx *fasthttp.RequestCtx) (ReportBolgerRetData2, error) {

	data := ReportBolgerRetData2{}
	where := "1=1"
	oderBy := "a.bloger_date"
	oderType := "desc"
	if param.ProxyId != "" {
		where += " and a.uid=" + param.ProxyId
	}
	if param.ParentId > 0 {
		where += " and b.parent_id=" + strconv.Itoa(param.ParentId)
	}
	if param.StartTime != "" {
		StartTime, _ := helper.TimeToLoc(param.StartTime, loc)
		where += " and a.bloger_date>='" + strconv.FormatInt(StartTime, 10) + "'"
	}
	if param.EndTime != "" {
		EndTime, _ := helper.TimeToLoc(param.EndTime, loc)
		where += " and a.bloger_date<='" + strconv.FormatInt(EndTime, 10) + "'"
	}
	if param.OperatorId != "" {
		where += " and c.id=" + param.OperatorId
	}
	if param.BusinessId != "" {
		where += " and d.id=" + param.BusinessId
	}
	if param.OderBY != "" {
		oderBy = param.OderBY
	}
	if param.OderType != "" {
		oderType = param.OderType
	}

	loginUser := GetLoginUser(ctx)
	if loginUser.Operator != "" {
		where += " and c.id=" + loginUser.Operator
	}
	if loginUser.Businsess != "" {
		where += " and d.id=" + loginUser.Businsess
	}

	table := "tbl_bloger_list"

	join := " left join tbl_member_base as b on b.uid=a.uid"          //玩家表
	join += " left join tbl_operator_info as c on c.id=b.operator_id" //渠道表
	join += " left join tbl_business_info as d on d.id=b.business_id" //业务员表

	join += " left join tbl_member_accu as e on e.uid=a.uid"
	join += " left join view_member_adjust as f on f.uid=a.uid"

	join += " left join tbl_proxy_accu as b1 on b1.uid=a.uid and b1.level=1"
	join += " left join tbl_proxy_accu as b2 on b2.uid=a.uid and b2.level=2"
	join += " left join tbl_proxy_accu as b3 on b3.uid=a.uid and b3.level=3"

	order := oderBy + " " + oderType

	offset := (param.Page - 1) * param.PageSize
	if param.Page >= 1 {
		count := "select count(1) from " + table + " as a " + join + " where " + where

		err := meta.MerchantDB.Get(&data.T, count)
		if err != nil {

			return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), count), "数据库错误")
		}

		if data.T == 0 {
			return data, nil
		}
	}
	field := "a.uid,a.bloger_name,a.bloger_date" +
		",ifnull(e.withdraw,0) withdraw" +
		",ifnull(e.deposit,0) deposit" +
		",ifnull(f.amount,0) up_score" +
		",ifnull(b1.team_num,0) lv1_team_num,ifnull(b1.deposit,0) lv1_deposit,ifnull(b1.deposit_num,0) lv1_deposit_num,ifnull(b1.withdraw,0) lv1_withdraw" +
		",ifnull(b2.team_num,0) lv2_team_num,ifnull(b2.deposit,0) lv2_deposit,ifnull(b2.deposit_num,0) lv2_deposit_num,ifnull(b2.withdraw,0) lv2_withdraw" +
		",ifnull(b3.team_num,0) lv3_team_num,ifnull(b3.deposit,0) lv3_deposit,ifnull(b3.deposit_num,0) lv3_deposit_num,ifnull(b3.withdraw,0) lv3_withdraw" +
		",ifnull(c.id,'') operator_id" +
		",ifnull(c.operator_name,'') operator_name" +
		",ifnull(d.id,'') business_id" +
		",ifnull(d.account_name,'') as business_name"

	query := "select " + field + " from " + table + "  as a " + join + " where " + where + " order by " + order + " limit " + strconv.Itoa(param.PageSize) + " offset " + strconv.Itoa(offset)
	fmt.Println(query)
	err := meta.MerchantDB.Select(&data.D, query)
	if err != nil && err != sql.ErrNoRows {

		return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), query), "数据库错误")
	}
	return data, nil
}

func BolgerUpdate(param BolgerData, isDelete int) error {
	bolger, err := GetBolger(param.Uid)
	if err != nil {
		return err
	}
	table := "tbl_bloger_list"
	query := ""
	if isDelete == 0 {
		if bolger.Uid != "" {
			if param.BlogerDate == 0 {
				param.BlogerDate = int(time.Now().Unix())
			}
			query, _, _ = dialect.Update(table).Set(param).Where(g.Ex{"uid": bolger.Uid}).ToSQL()
		} else {
			param.BlogerDate = int(time.Now().Unix())
			query, _, _ = dialect.Insert(table).Rows(param).ToSQL()
		}

	} else {
		query, _, _ = dialect.Delete(table).Where(g.Ex{"uid": param.Uid}).ToSQL()
	}

	_, err = meta.MerchantDB.Exec(query)
	if err != nil {

		return errors.New("数据库错误")
	}
	return nil
}

func GetUnbindList(param ReportProxyParam, ctx *fasthttp.RequestCtx) (ReportUnbindRetData, error) {

	data := ReportUnbindRetData{}
	where := "a.unbindParentId>0"
	oderBy := "b.created_at"
	oderType := "desc"
	if param.ProxyId != "" {
		where += " and a.uid=" + param.ProxyId
	}
	if param.ParentId > 0 {
		where += " and a.unbindParentId=" + strconv.Itoa(param.ParentId)
	}
	if param.StartTime != "" {
		StartTime, _ := helper.TimeToLoc(param.StartTime, loc)
		where += " and b.created_at>='" + strconv.FormatInt(StartTime, 10) + "'"
	}
	if param.EndTime != "" {
		EndTime, _ := helper.TimeToLoc(param.EndTime, loc)
		where += " and b.created_at<='" + strconv.FormatInt(EndTime, 10) + "'"
	}
	if param.OperatorId != "" {
		where += " and c.id=" + param.OperatorId
	}
	if param.BusinessId != "" {
		where += " and d.id=" + param.BusinessId
	}
	if param.OderBY != "" {
		oderBy = param.OderBY
	}
	if param.OderType != "" {
		oderType = param.OderType
	}

	loginUser := GetLoginUser(ctx)
	if loginUser.Operator != "" {
		where += " and c.id=" + loginUser.Operator
	}
	if loginUser.Businsess != "" {
		where += " and d.id=" + loginUser.Businsess
	}

	table := "tbl_proxy_base"

	join := " left join tbl_member_base as b on b.uid=a.uid"          //玩家表
	join += " left join tbl_operator_info as c on c.id=b.operator_id" //渠道表
	join += " left join tbl_business_info as d on d.id=b.business_id" //业务员表
	join += " left join tbl_member_accu as e on e.uid=a.uid"

	order := oderBy + " " + oderType

	offset := (param.Page - 1) * param.PageSize
	if param.Page >= 1 {
		count := "select count(1) from " + table + " as a " + join + " where " + where

		err := meta.MerchantDB.Get(&data.T, count)
		if err != nil {

			return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), count), "数据库错误")
		}

		if data.T == 0 {
			return data, nil
		}
	}
	field := "a.uid,a.unbindParentId as parent_id,b.created_at" +
		",ifnull(e.withdraw,0) withdraw" +
		",ifnull(e.deposit,0) deposit" +
		",ifnull(c.id,'') operator_id" +
		",ifnull(c.operator_name,'') operator_name" +
		",ifnull(d.id,'') business_id" +
		",ifnull(d.account_name,'') as business_name"

	query := "select " + field + " from " + table + "  as a " + join + " where " + where + " order by " + order + " limit " + strconv.Itoa(param.PageSize) + " offset " + strconv.Itoa(offset)

	err := meta.MerchantDB.Select(&data.D, query)
	if err != nil && err != sql.ErrNoRows {

		return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), query), "数据库错误")
	}

	//统计
	sfield := "COUNT(1) num," +
		"ifnull(sum(e.withdraw),0) withdraw," +
		"ifnull(sum(e.deposit),0) deposit"
	query = "select " + sfield + " from " + table + "  as a " + join + " where " + where
	err = meta.MerchantDB.Get(&data.S, query)
	if err != nil && err != sql.ErrNoRows {
		return data, pushLog(fmt.Errorf("%s,[%s]", err.Error(), query), "数据库错误")
	}
	return data, nil
}

func BatchUpdateState(param BatchUpdateStateParam, ctx *fasthttp.RequestCtx) error {
	//获取全部下级
	uids := GetAgentTeam(param.ParentId, param.ParentId)
	arrUids := strings.Split(uids, ",")

	//分块处理 1000一块
	for i := 0; i < len(arrUids); i += 1000 {
		end := i + 1000
		if end > len(arrUids) {
			end = len(arrUids)
		}
		exexUsers := arrUids[i:end]
		BatchUpdate(exexUsers, "tbl_member_base", "state", param.State)
	}
	query := "update tbl_member_base set state=" + param.State + " where uid in(" + uids + ")"
	_, err := meta.MerchantDB.Exec(query)
	if err != nil {
		return err
	}
	return nil
}

// 获取伞下全部用户 以及自身 列表
func GetAgentTeam(uid string, uids string) string {
	data := []string{}
	query := "select uid from tbl_proxy_base where parent_id in(" + uid + ")"
	err := meta.MerchantDB.Select(&data, query)
	if err != nil && err != sql.ErrNoRows {
		return ""
	}
	if len(data) > 0 {
		newuid := strings.Join(data, ",")
		uids += "," + newuid
		return GetAgentTeam(newuid, uids)
	} else {
		return uids
	}
}

// 获取n级用户列表
func GetAgentTeamByDepth(uid string, length int) string {
	uids := "0"
	queryUids := uid
	for i := 1; i < length+1; i++ {
		data := []string{}
		query := "select uid from tbl_proxy_base where parent_id in(" + queryUids + ")"
		err := meta.MerchantDB.Select(&data, query)
		if err != nil && err != sql.ErrNoRows {
			return ""
		}
		if len(data) > 0 {
			queryUids = strings.Join(data, ",")
			uids = queryUids
		} else {
			return "0"
		}
	}
	return uids
}

// 获取n全部用户列表
func GetAgentTeamByLenth(uid string, lenth int) string {
	data := []string{}
	uids := uid
	queryUids := uid
	for i := 1; i < lenth+1; i++ {
		query := "select uid from tbl_proxy_base where parent_id in(" + queryUids + ")"
		err := meta.MerchantDB.Select(&data, query)
		if err != nil && err != sql.ErrNoRows {
			return ""
		}
		if len(data) > 0 {
			queryUids = strings.Join(data, ",")
			uids += "," + queryUids
		} else {
			return uids
		}
	}
	return uids
}

func GetAgentTeamByLenth2(uid string, length int) string {
	data := []string{}
	uids := "0"
	queryUids := uid
	for i := 1; i < length+1; i++ {
		query := "select uid from tbl_proxy_base where parent_id in(" + queryUids + ")"
		err := meta.MerchantDB.Select(&data, query)
		if err != nil && err != sql.ErrNoRows {
			return ""
		}
		if len(data) > 0 {
			queryUids = strings.Join(data, ",")
			uids += "," + queryUids
		} else {
			return uids
		}
	}
	return uids
}
func GetParent(uid int) string {
	parentId := "0"
	query := "select parent_id from tbl_proxy_base where uid=" + strconv.Itoa(uid)
	meta.MerchantDB.Get(&parentId, query)
	return parentId
}
